SQL Editor - Build Subquery

The Add Query button allows you to create additional queries for your conditional WHERE clause. A subquery is frequently used in the where clause to limit the number of records returned in the main query. You can create a subquery in the Visual Designer or Free Type.

Scalar Subquery

Some databases allow entire SQL statements to be used as a field in the SELECT clause of another query. As each row is processed in the main query, the subquery is called to fill in the value for that particular field. These subqueries must return a single value (or no value) for each row in the main query.

select last_name, first_name, ID, (select SSN from SSN_TABLE where ID=ID) as "SSN"
from person_table

This technique can be used for retrieving a data element from a table if you don’t wish to include that table in the main query. This can be a useful way to avoid the use of outer joins, which can negatively impact query performance.

  1. Create a calculated field.
  2. Click the ellipsis next to Field to define the calculated field. Instead of including simple fields and expressions, you will actually create an entire SQL statement, enclosed in a set of parenthesis. Remember that the scalar subquery must link to the main query and must return zero or one row.
  3. As for all calculated fields, make sure to give the field an alias.

Correlated Subquery

Some queries require that the output be limited by the results of a subquery. The subquery is linked to the main query (in the very last line). As each row from the main query is examined, it will be compared against the results of the subquery. These types of subqueries are called “correlated” as the results of the main query are dependent on the results of the subquery. In most cases the main query and the subquery use the same table. Whenever a table is used multiple times in the same query, it is important to add a table alias for each instance of the table (Argos adds table aliases automatically).

select last_name, first_name, ID
from people
where status="A"
and select max(effective_date) "MaxDate"
  from people as people1
  where people_id=people1_id

Building a correlated subquery using the Visual SQL Builder is not difficult, but does require several steps:

Step #1: Build the main query
Step #2: Build the correlated subquery

From the main query design, click the Add Query button to begin building your subquery. You do not need to add the starting and ending parenthesis, Argos will do that automatically for any subquery.

Step #3: Add the subquery to the WHERE clause of the main query

Now that you’ve built your main query and your subquery, you only need to place the subquery into the appropriate section of the main query. To perform this linkage, click the Link with Query button on your subquery. This opens the WHERE clause in your main query and asks you what operator you would like to use.

This image is the query dialog and contains the text DEPARTMENT_ID.

Subquery Options

Button Description
Add button Add the subquery to the main query's WHERE clause.
Edit button Edit the subquery.
delete button Delete the subquery.

 

Non-Correlated Subquery

Sometimes it is useful to create a subquery that is not directly related to the main query. It is possible to accomplish this with some tricky join logic, but a “non-correlated” subquery is often easier. These types of subqueries are generally not tied to a particular field in the main query using the “=” operator, rather they typically use other operators such as <. >, IN, NOT IN, EXISTS and NOT EXISTS. These are called non-correlated subqueries as the subquery is not really dependent on the main query data, the results of the subquery are the same regardless of which row is being examined in the main query. This can also result in faster performance for many queries.

This image shows selections available when creating a Sub Query. Selection include radio buttons such as EXISTS, NOT EXISTS, IN, NOT IN, less than, less than or equal to, equals, greater than or equal to, greater than, and not equal.

Sample Non-Correlated Subquery

select employees.last_name, employees.first_name
from hr.employees employees
where employees.department_id in
 (select departments.department_id
  from hr.departments departments
  where departments.department_name = "Finance")

Inline View Subquery

An inline view subquery allows you to create a query, the results of which can be used as a table in a FROM clause. Once the inline view subquery is created, it is added to Visible Fields (SELECT) tab for the main query as if it were a table. See Inline View Subquery in the DataBlock Designer Guide for more information.

Some details to keep in mind when using inline views:

 

Need More Help?

If you need additional help creating DataBlocks, refer to the Getting Started with DataBlocks page.

To read step-by-step tutorials on how to create subqueries, refer to Example 10 (Scalar Subquery), Example 11( Correlated Subquery), or Example 12 (Non-Correlated Subquery), in the DataBlock Designer Guide.